package com.mingke.java.service.Impl;

import com.mingke.java.entity.DataBackupEntity;
import com.mingke.java.params.AdvancedOperationParams;
import com.mingke.java.params.SimpleOperationParams;
import com.mingke.java.service.DataBackupService;
import com.mingke.java.task.DataOperationTask;
import com.mingke.java.task.InsertTask;
import com.mingke.java.task.TaskDispatch;
import com.mingke.java.utils.DataSourceUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.BufferedWriter;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.Date;
import java.util.Optional;
import java.util.Random;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.LinkedBlockingQueue;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.TimeUnit;

@Service
public class DataBackupServiceImpl implements DataBackupService {

    static Integer threadNum = 8;

    private static ThreadPoolExecutor executor  = new ThreadPoolExecutor(threadNum, 12, 60L, TimeUnit.SECONDS, new LinkedBlockingQueue<Runnable>(16));

    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    /**
     * 记录一下遇到的问题
     * 1. 原来打算拼SQL然后把SQL写入到文件，后来发现费时费力，可以用PrepareStatement预览要执行的SQL
     * 2. 原来打算数据库备份也直接执行source xx.sql命令实现数据备份，后来发现这是个shell命令，JDBC并不支持
     * @param dataBackupEntity
     * @return
     */
    @Override
    public Boolean addBackup(DataBackupEntity dataBackupEntity) {
        //TODO 根据前端传过来的id获取到数据源连接信息
        String URL = "jdbc:mysql://localhost:3306/zzuli";
        //备份到的数据库的名称
        String backupURL = "jdbc:mysql://localhost:3306/mysql";
        String username = "root";
        String password = "123456789";
        String filePath = "/Users/vase/Documents/Dev/sql/copy.sql";

        //读取源数据库
        DataSourceUtils dataSourceUtils = new DataSourceUtils();
        //读取目标数据库
        DataSourceUtils dataSourceUtils1 = new DataSourceUtils();
        dataSourceUtils.initSimpleConnection(URL, username, password);
        dataSourceUtils1.initSimpleConnection(backupURL, username, password);
        ResultSet resultSet = dataSourceUtils.doSQL(dataSourceUtils.createSelectSQL(dataBackupEntity.getTableName()), new Date());
        if(resultSet != null) {
            try {
                //初始化文件写入缓冲区
                BufferedWriter bufferedWriter = Files.newBufferedWriter(Path.of(filePath), StandardCharsets.UTF_8);

                //解析ResultSet中数据的结构信息
                ResultSetMetaData resultSetMetaData =  resultSet.getMetaData();
                //获取列的个数
                int columnCount = resultSetMetaData.getColumnCount();
                //获取指定下标列所属于的表的表名，因为此处的SQL执行可能涉及到联表查询，所以这里有这样的设计
                String oldTableName = resultSetMetaData.getTableName(1);
                String backupTableName = oldTableName + "_" + getSuffix();

                //同步环境下用StringBuffer，多线程并发用StringBuilder
                //拼接原始模版SQL
                StringBuffer headBuffer = new StringBuffer();
                StringBuffer tailBuffer = new StringBuffer();
                headBuffer.append("INSERT INTO ").append(backupTableName).append(" ");
                tailBuffer.append(" VALUES (");
                headBuffer.append('(');
                //获取列名
                for(int i = 1; i <= columnCount; i++) {
                    String columnName = resultSetMetaData.getColumnName(i);
                    headBuffer.append(columnName);
                    tailBuffer.append('?');
                    if(i != columnCount) {
                        headBuffer.append(", ");
                        tailBuffer.append(", ");
                    }
                }
                headBuffer.append(')');
                tailBuffer.append(')');
                headBuffer.append(tailBuffer);

                //写入头部说明信息
                bufferedWriter.write("/*");
                bufferedWriter.write("\n");
                bufferedWriter.write("  SQL备份时间: "+LocalDateTime.now());
                bufferedWriter.write("\n");
                bufferedWriter.write("  SQL备份原数据表名: "+ oldTableName);
                bufferedWriter.write("\n");
                bufferedWriter.write("  Create By DataColl V1.0");
                bufferedWriter.write("\n");
                bufferedWriter.write("*/");
                bufferedWriter.write("\n");

                //获取到创建数据表的SQL
                String createSQL = "";
                String showSQL = "show create table "+ oldTableName;
                ResultSet createResultSet = dataSourceUtils.doSQL(showSQL);
                while (createResultSet.next()) {
                    createSQL = Optional.ofNullable(createResultSet.getString("Create Table")).orElse("创建表SQL未获取到，请检查表名是否有误");
                }
                //替换原有表名，注意此处仅需要替换第一个匹配到的也就是原来的表名
                createSQL = createSQL.replaceFirst(oldTableName, backupTableName);
                bufferedWriter.write(createSQL);
                bufferedWriter.write("\n\n\n\n");
                //执行创建新表的SQL，这样执行速度会很慢，可以考虑另外开一个线程单独执行
                if(dataBackupEntity.getType() == 2) {
                    dataSourceUtils1.doSQL(createSQL);
                }

                //拼接形成原始SQL
                String rawSQL = headBuffer.toString();
                //获取列值，拼接可执行版SQL
                while (resultSet.next()) {
                    Object objs[] = new Object[columnCount];
                    //将列值取出直接放到Object数组中
                    for(int i = 1; i <= columnCount; i++) {
                        objs[i-1] = resultSet.getObject(i);
                    }
                    //使用SQL生成器生成可执行SQL
                    String SQL = dataSourceUtils.generateSQL(rawSQL, objs);
                    bufferedWriter.write(SQL);
                    bufferedWriter.newLine();
                    //将SQL记录插入到新的数据库
                    if(dataBackupEntity.getType() == 2) {
                        dataSourceUtils1.doSQL(SQL);
                    }
                }
                //flush一下，把所有缓冲区的信息写入到磁盘文件中
                bufferedWriter.flush();
                bufferedWriter.close();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                //最后记得关闭连接信息
                dataSourceUtils.close();
                dataSourceUtils1.close();
            }

            return true;
        }else {
            return false;
        }
    }

    /**
     * 1. 使用JDBC Batch实际上没啥意义，优化了半天也就20%的提升，看不见提升的原因是没有在JDBC URL连接上选择打开批处理
     * 数据插入的速度
     * 2. 当前线程能继续快速执行是因为其他线程崩溃使得内存资源由剩余线程独享
     */
    @Override
    public void init() {
        String URL = "jdbc:mysql://127.0.0.1:3306/copy?rewriteBatchedStatements=true";
        String username = "root";
        String password = "123456789";

        String insertSql = "INSERT INTO zzuli_user (nick_name, password, age, salary, create_time) VALUES (?, ?, ?, ?, ?)";
        String cleanSql = "truncate zzuli_user";

        DataSourceUtils dataSourceUtils = new DataSourceUtils();
        dataSourceUtils.initSimpleConnection(URL, username, password);
        //清空数据表
        dataSourceUtils.doSQL(cleanSql);
        System.out.println("删除数据成功");
        String user = "dsdsds";
        String pass = "asdsad34ds";

    }



    @Override
    public void doSimpleOperationTask(SimpleOperationParams params) {
        DataOperationTask task = new DataOperationTask();
        task.simpleOperation(params);
    }

    @Override
    public void doAdvancedOperationTask(AdvancedOperationParams params) {
        DataOperationTask task = new DataOperationTask();
        task.advancedOperation(params);
    }

    //使用批量插入的方法
    @Override
    public void init1() {
        //新增批处理参数
//        String URL = "jdbc:mysql://127.0.0.1:3306/copy?rewriteBatchedStatements=true";
//        String username = "root";
//        String password = "123456789";
//
//        String insertSql = "INSERT INTO zzuli_user (nick_name, password, age, salary, create_time) VALUES (?, ?, ?, ?, ?)";
//        String cleanSql = "truncate zzuli_user";
//
//        DataSourceUtils dataSourceUtils = new DataSourceUtils();
//        dataSourceUtils.initSimpleConnection(URL, username, password);
//        //清空数据表
//        dataSourceUtils.doSQL(cleanSql);
//        System.out.println("删除数据成功");
//
//        dataSourceUtils.batchSetRawSQL(insertSql);
//
//        Random random = new Random();
//        long begin = System.currentTimeMillis();
//        for(int j = 0; j < 2000000; j++) {
//            int age = random.nextInt(80)+20;
//            double salary = random.nextDouble()*10000;
//            String createTime = date2String(new Date());
//
//            dataSourceUtils.batchAddItem("user"+j, "123456", age, salary, createTime);
//
//            if(j % 100 == 0) {
//                dataSourceUtils.batchExecutor();
//                long end = System.currentTimeMillis();
//                System.out.println("每100条数据执行时间: "+(end-begin)+"ms");
//                begin = end;
//            }
//        }
//
//        dataSourceUtils.batchExecutor();
//
//        dataSourceUtils.close();
    }


    /**
     * 将时间类型格式化成字符串的方法
     * @param date 时间
     * @return 格式化后的字符串
     */
    public String date2String(Date date) {
        return format.format(date);
    }


    /**
     * 将时间戳类型格式化成字符串的方法
     * @param timestamp 时间戳
     * @return 格式化后的时间字符串
     */
    public String timeStamp2String(Timestamp timestamp) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        return format.format(new Date(timestamp.getTime()));
    }

    /**
     * 生成时间标记的后缀
     * @return
     */
    public String getSuffix() {
        Timestamp timestamp = Timestamp.valueOf(LocalDateTime.now());
        SimpleDateFormat format = new SimpleDateFormat("yyyy_MM_dd_HH_mm_ss");
        return format.format(new Date(timestamp.getTime()));
    }
}
